package cn.edu.tju.core.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;

/**
 * ClassName: DatabaseInitializer
 * Package: cn.edu.tju.core.config
 * Description: 数据库初始化器 - 只在首次启动时执行SQL脚本
 *
 * @Author Niu Wenhang
 * @Create 2025/9/21 19:50
 * @Version 1.0
 */
@Component
public class DatabaseInitializer implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        // 检查是否已经初始化过（通过检查某个表是否存在数据）
        if (isDatabaseInitialized()) {
            System.out.println("数据库已经初始化，跳过SQL脚本执行");
            return;
        }

        System.out.println("首次启动，开始执行数据库初始化脚本");
        executeSqlScript();
    }

    private boolean isDatabaseInitialized() {
        try {
            // 首先尝试检查初始化状态表
            try {
                String checkInitSql = "SELECT COUNT(*) FROM database_init_status WHERE initialized = 1";
                Integer initCount = jdbcTemplate.queryForObject(checkInitSql, Integer.class);
                if (initCount != null && initCount > 0) {
                    System.out.println("数据库已标记为已初始化");
                    return true;
                }
            } catch (Exception e) {
                // 初始化状态表不存在，继续检查用户表
            }
            
            // 检查用户表是否有数据，如果有数据说明已经初始化过
            String countSql = "SELECT COUNT(*) FROM users";
            Integer count = jdbcTemplate.queryForObject(countSql, Integer.class);
            if (count != null && count > 0) {
                // 标记数据库为已初始化
                markDatabaseAsInitialized();
                return true;
            }
            return false;
        } catch (Exception e) {
            // 如果表不存在或查询失败，说明需要初始化
            System.out.println("检查数据库初始化状态时出错: " + e.getMessage());
            return false;
        }
    }
    
    private void markDatabaseAsInitialized() {
        try {
            // 创建初始化状态表
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS database_init_status (id INT PRIMARY KEY, initialized BOOLEAN)");
            // 插入初始化状态记录
            jdbcTemplate.execute("INSERT INTO database_init_status (id, initialized) VALUES (1, TRUE) ON DUPLICATE KEY UPDATE initialized = TRUE");
            System.out.println("数据库初始化状态已标记");
        } catch (Exception e) {
            //System.out.println("标记数据库初始化状态失败: " + e.getMessage());
        }
    }

    private void executeSqlScript() {
        try {
            ClassPathResource resource = new ClassPathResource("data.sql");
            String sqlScript = new String(Files.readAllBytes(resource.getFile().toPath()), StandardCharsets.UTF_8);
            
            // 分割SQL语句并执行
            String[] sqlStatements = sqlScript.split(";");
            int successCount = 0;
            int skipCount = 0;
            
            for (String sql : sqlStatements) {
                sql = sql.trim();
                if (!sql.isEmpty() && !sql.startsWith("--")) {
                    try {
                        jdbcTemplate.execute(sql);
                        System.out.println("✓ 执行SQL成功: " + sql.substring(0, Math.min(50, sql.length())) + "...");
                        successCount++;
                    } catch (Exception e) {
                        // 检查是否是重复数据错误
                        if (e.getMessage().contains("Duplicate entry") || e.getMessage().contains("duplicate key")) {
                            System.out.println("⚠ 跳过重复数据: " + sql.substring(0, Math.min(50, sql.length())) + "...");
                            skipCount++;
                        } else {
                            System.err.println("✗ 执行SQL失败: " + sql.substring(0, Math.min(50, sql.length())) + "...");
                            System.err.println("错误: " + e.getMessage());
                        }
                    }
                }
            }
            System.out.println("数据库初始化完成 - 成功: " + successCount + ", 跳过: " + skipCount);
            // 标记数据库为已初始化
            markDatabaseAsInitialized();
        } catch (IOException e) {
            System.err.println("读取SQL脚本失败: " + e.getMessage());
        }
    }
}
